/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.struts.modele;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author jackson
 */
public class QueryBD {

    private Connection cx;
    private Statement st;
    private Promotion promotion = new Promotion();
    private Enseignant enseignant = new Enseignant();
    private Etudiant etudiant = new Etudiant();
    private Matiere matiere = new Matiere();
    private Cours cours = new Cours();
    private Enseigne enseigne = new Enseigne();
    private EstAbsent estAbsent = new EstAbsent();
    private Assiste assiste = new Assiste();
    private Segmi segmi = new Segmi(null);

    //Connexion à la BD et chargement du driver
    public QueryBD() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception ex) {
            System.err.println("Erreur lors du chargement du driver" + ex);
            System.exit(1);
        }
        try {
            String url = "jdbc:mysql://localhost:8889/absence";
            //String url = "jdbc:mysql://localhost:3306/absence";

            cx = DriverManager.getConnection(url, "root", "root");
            //cx = DriverManager.getConnection(url, "root", "");

        } catch (Exception ex) {
            System.err.println("Erreur lors de la cx à la base" + ex);
            System.exit(1);
        }

    }

    //Gère la connxion des enseignant
    public boolean ConnexionEnseignant(String num, String password) {
        try {
            st = cx.createStatement();
            
            
            ResultSet rs = st.executeQuery("SELECT * FROM promotion, enseignant, enseigne, matiere, inscrit "
                    + "WHERE enseigne.idmatiere = matiere.idmatiere "
                    + "AND enseigne.nenseignant = enseignant.nenseignant "
                    + "AND inscrit.idmatiere = matiere.idmatiere "
                    + "AND inscrit.npromotion = promotion.npromotion "
                    + "AND enseignant.nenseignant = " + num + " "
                    + "AND enseignant.PASSWORD ='" + password +"' "
                    + "group by promotion.npromotion");

            
            int i = 0;
            while(rs.next()) {
                if(i==0){
                    enseignant = new Enseignant(rs.getInt("enseignant.nenseignant"), rs.getString("enseignant.nom"), 
                            rs.getString("enseignant.prenom"), rs.getString("enseignant.telephone"), rs.getString("enseignant.adresse"), 
                            rs.getDate("enseignant.naissance"), rs.getString("enseignant.email"), rs.getString("enseignant.password"),null,null);
                    i++;
                }
                promotion = new Promotion(rs.getInt("promotion.npromotion"), rs.getString("promotion.nom"), null, null);
                enseignant.getPromotions().add(promotion);
            }
            
            ResultSet rsMatiere = st.executeQuery("SELECT * FROM promotion, enseignant, enseigne, matiere, inscrit "
                    + "WHERE enseigne.idmatiere = matiere.idmatiere "
                    + "AND enseigne.nenseignant = enseignant.nenseignant "
                    + "AND inscrit.idmatiere = matiere.idmatiere "
                    + "AND inscrit.npromotion = promotion.npromotion "
                    + "AND enseignant.nenseignant = " + num + " "
                    + "AND enseignant.PASSWORD ='" + password +"' "
                    + "group by matiere.idmatiere");
            
            while(rsMatiere.next()){
                
                matiere = new Matiere(rsMatiere.getInt("matiere.idmatiere"), rsMatiere.getString("matiere.nom"), null, null);
                enseignant.getMatieres().add(matiere);
                
            }
            
            rs.close();
            st.close();
            cx.close();

        } catch (Exception ex) {
            System.err.println("Erreur lors de la cx à la base" + ex);
            System.exit(1);
        }
        return true;
    }
    
    
    //Gère la connxion des etudiant
    public boolean ConnexionEtudiant(String num, String password) {
        try {

            st = cx.createStatement();
            
            ResultSet rsPromo = st.executeQuery("SELECT * FROM promotion p, etudiant e, estabsent ea, matiere m "
                    + "WHERE p.npromotion = e.npromotion AND e.netudiant = ea.netudiant "
                    + "AND m.idmatiere = ea.idmatiere AND e.netudiant ="+ num +" AND e.password ='"+ password +"'");
            int i = 0;
            while(rsPromo.next()){
               
                if(i==0) {

                    etudiant = new Etudiant(rsPromo.getInt("e.netudiant"), rsPromo.getString("e.nom"), 
                        rsPromo.getString("e.prenom"), rsPromo.getString("e.telephone"), 
                        rsPromo.getString("e.adresse"), rsPromo.getDate("e.naissance"), 
                        rsPromo.getString("e.email"), rsPromo.getString("e.password"), null, null, null);
                
                    promotion = new Promotion(rsPromo.getInt("p.npromotion"), rsPromo.getString("p.nom"),null,null);
                    etudiant.setPromotion(promotion);
                    promotion.getEtudiants().add(etudiant);

                    i++;
                }
                
                matiere = new Matiere(rsPromo.getInt("m.idmatiere"), rsPromo.getString("m.nom"),null, null);
                etudiant.getMatieres().add(matiere);
                promotion.getMatieres().add(matiere);
                estAbsent = new EstAbsent(matiere, etudiant, rsPromo.getInt("nbtotalabsence"));
                matiere.getAbsence().put(etudiant, estAbsent);
            }
            
            rsPromo.close();
            st.close();
            cx.close();
            
        } catch (Exception ex) {
            System.out.println("Erreur lors de la cx à la base" + ex);
            System.exit(1);
        }
        return true;
    }
    
    public boolean recupAbsence(Etudiant etu, Matiere mat){
        try{
       
            String url = "jdbc:mysql://localhost:8889/absence";
            //String url = "jdbc:mysql://localhost:3306/absence";

            cx = DriverManager.getConnection(url, "root", "root");
            //cx = DriverManager.getConnection(url, "root", "");
            
            st = cx.createStatement();
            
            ResultSet rsAbsence = st.executeQuery("select * from etudiant, assiste, cours, matiere "
                    + "where assiste.netudiant = etudiant.netudiant "
                    + "and assiste.ncours=cours.ncours "
                    + "and cours.idmatiere = matiere.idmatiere "
                    + "and etudiant.netudiant = " + etu.getNetudiant()
                    + " and presence='absent' "
                    + "and matiere.nom= '" + mat.getNom() + "'");
            
            while(rsAbsence.next()){

                cours = new Cours(rsAbsence.getInt("ncours"), rsAbsence.getString("datecours"), 
                        rsAbsence.getString("heuredebut"), rsAbsence.getString("heurefin"), 
                        matiere, enseignant);
                
                etu.getMatieres().add(mat);
                mat.getCours().add(cours);

            }
            
        }catch(Exception ex){
            System.out.println("Erreur lors de la cx à la base" + ex);
            System.exit(1);
        }
        return true;
    }
    
     public boolean recupAbsenceEns(int nEtu, Matiere mat){
        try{
       
            String url = "jdbc:mysql://localhost:8889/absence";
            //String url = "jdbc:mysql://localhost:3306/absence";

            cx = DriverManager.getConnection(url, "root", "root");
            //cx = DriverManager.getConnection(url, "root", "");
            
            st = cx.createStatement();
            
            ResultSet rsPromo = st.executeQuery("SELECT * FROM promotion p, etudiant e, estabsent ea, matiere m "
                    + "WHERE p.npromotion = e.npromotion AND e.netudiant = ea.netudiant "
                    + "AND m.idmatiere = ea.idmatiere AND e.netudiant ="+ nEtu);
            int i = 0;
            while(rsPromo.next()){
               
                if(i==0) {

                    etudiant = new Etudiant(rsPromo.getInt("e.netudiant"), rsPromo.getString("e.nom"), 
                        rsPromo.getString("e.prenom"), rsPromo.getString("e.telephone"), 
                        rsPromo.getString("e.adresse"), rsPromo.getDate("e.naissance"), 
                        rsPromo.getString("e.email"), rsPromo.getString("e.password"), null, null, null);
                
                    i++;
                }
             
                etudiant.getMatieres().add(mat);
                estAbsent = new EstAbsent(mat, etudiant, rsPromo.getInt("nbtotalabsence"));
                mat.getAbsence().put(etudiant, estAbsent);
            }
            
            ResultSet rsAbsence = st.executeQuery("select * from etudiant, assiste, cours, matiere "
                    + "where assiste.netudiant = etudiant.netudiant "
                    + "and assiste.ncours=cours.ncours "
                    + "and cours.idmatiere = matiere.idmatiere "
                    + "and etudiant.netudiant = " + nEtu
                    + " and presence='absent' "
                    + "and matiere.idMatiere= " + mat.getIdmatiere() );

            while(rsAbsence.next()){
                
                cours = new Cours(rsAbsence.getInt("ncours"), rsAbsence.getString("datecours"), 
                        rsAbsence.getString("heuredebut"), rsAbsence.getString("heurefin"), 
                        matiere, enseignant);
            
                etudiant.getMatieres().add(mat);
                mat.getCours().add(cours);
 
            }
            
            System.out.println(etudiant.getPrenom());
            
        }catch(Exception ex){
            System.out.println("Erreur lors de la cx à la base" + ex);
            System.exit(1);
        }
        return true;
    }
     
     public boolean creerCours(String date, String heureDebut, String heureFin, String nomMat, int nens){
        try{
       
            String url = "jdbc:mysql://localhost:8889/absence";
            //String url = "jdbc:mysql://localhost:3306/absence";

            cx = DriverManager.getConnection(url, "root", "root");
            //cx = DriverManager.getConnection(url, "root", "");
            
            st = cx.createStatement();
            
            ResultSet rsMat = st.executeQuery("select * from matiere "
                    + "where nom = '"+nomMat+"'");
            
            if(rsMat.next()){
                matiere = new Matiere(rsMat.getInt("idmatiere"), rsMat.getString("nom"), null, null);
            }
            
            st.executeUpdate("INSERT INTO `cours` (`datecours`, `heuredebut`, `heurefin`, `idmatiere`, `nenseignant`) "            
                    + "VALUES ('" + date + "', '" + heureDebut + "', '" + heureFin + "', " + matiere.getIdmatiere() + ", " + nens + ")");
            
            ResultSet rsCours = st.executeQuery("select * from cours "
                    + "where datecours = '" + date + "' "
                    + "and heuredebut = '" + heureDebut + "' "
                    + "and heurefin = '" + heureFin + "' "
                    + "and idmatiere = " + matiere.getIdmatiere() + " "
                    + "and nenseignant = " + nens );
            
            if(rsCours.next()){
                cours = new Cours(rsCours.getInt("ncours"),rsCours.getString("datecours"), rsCours.getString("heuredebut"), rsCours.getString("heurefin"), matiere, enseignant);
                cours.setMatiere(matiere);
            }
 
        }catch(Exception ex){
            System.out.println("Erreur lors de la cx à la base" + ex);
            System.exit(1);
        }
        return true;
    }
    
     public boolean appelPresent(String nomEtu, int ncours){
        try{
       
            String url = "jdbc:mysql://localhost:8889/absence";
            //String url = "jdbc:mysql://localhost:3306/absence";

            cx = DriverManager.getConnection(url, "root", "root");
            //cx = DriverManager.getConnection(url, "root", "");
            
            st = cx.createStatement();
            
            ResultSet rsEtu = st.executeQuery("select * from etudiant where nom = '" + nomEtu + "'");
            
            if(rsEtu.next()){
                 etudiant = new Etudiant(rsEtu.getInt("netudiant"), rsEtu.getString("nom"), 
                        rsEtu.getString("prenom"), rsEtu.getString("telephone"), 
                        rsEtu.getString("adresse"), rsEtu.getDate("naissance"), 
                        rsEtu.getString("email"), rsEtu.getString("password"), null, null, null);
            }
            
            st.executeUpdate("INSERT INTO `assiste` (`ncours`, `netudiant`, `presence`) "
                    + "VALUES (" + ncours + ", " + etudiant.getNetudiant() + ", 'present')");
            
 
        }catch(Exception ex){
            System.out.println("Erreur lors de la cx à la base" + ex);
            System.exit(1);
        }
        return true;
    }
     
     public boolean appelAbsent(String nomEtu, int ncours, int idMat){
        try{
       
            String url = "jdbc:mysql://localhost:8889/absence";
            //String url = "jdbc:mysql://localhost:3306/absence";

            cx = DriverManager.getConnection(url, "root", "root");
            //cx = DriverManager.getConnection(url, "root", "");
            
            st = cx.createStatement();
            
            ResultSet rsEtu = st.executeQuery("select * from etudiant where nom = '" + nomEtu + "'");
            
            if(rsEtu.next()){
                 etudiant = new Etudiant(rsEtu.getInt("netudiant"), rsEtu.getString("nom"), 
                        rsEtu.getString("prenom"), rsEtu.getString("telephone"), 
                        rsEtu.getString("adresse"), rsEtu.getDate("naissance"), 
                        rsEtu.getString("email"), rsEtu.getString("password"), null, null, null);
            }
  
            System.out.println("INSERT INTO `assiste` (`ncours`, `netudiant`, `presence`) "
                    + "VALUES (" + ncours + ", " + etudiant.getNetudiant() + ", 'absent')");
            
            st.executeUpdate("INSERT INTO `assiste` (`ncours`, `netudiant`, `presence`) "
                    + "VALUES (" + ncours + ", " + etudiant.getNetudiant() + ", 'absent')");
            
            System.out.println("UPDATE estabsent SET nbtotalabsence = nbtotalabsence + 1 WHERE idmatiere=" + idMat + " AND netudiant = " + etudiant.getNetudiant());
            
            st.executeUpdate("UPDATE estabsent SET nbtotalabsence = nbtotalabsence + 1 WHERE idmatiere=" + idMat + " AND netudiant = " + etudiant.getNetudiant() );

 
        }catch(Exception ex){
            System.out.println("Erreur lors de la cx à la base" + ex);
            System.exit(1);
        }
        return true;
    }
     
     public boolean appelExcuse(String nomEtu, int ncours){
        try{
       
            String url = "jdbc:mysql://localhost:8889/absence";
            //String url = "jdbc:mysql://localhost:3306/absence";

            cx = DriverManager.getConnection(url, "root", "root");
            //cx = DriverManager.getConnection(url, "root", "");
            
            st = cx.createStatement();
            
            ResultSet rsEtu = st.executeQuery("select * from etudiant where nom = '" + nomEtu + "'");
            
            if(rsEtu.next()){
                 etudiant = new Etudiant(rsEtu.getInt("netudiant"), rsEtu.getString("nom"), 
                        rsEtu.getString("prenom"), rsEtu.getString("telephone"), 
                        rsEtu.getString("adresse"), rsEtu.getDate("naissance"), 
                        rsEtu.getString("email"), rsEtu.getString("password"), null, null, null);
            }
            
            st.executeUpdate("INSERT INTO `assiste` (`ncours`, `netudiant`, `presence`) "
                    + "VALUES (" + ncours + ", " + etudiant.getNetudiant() + ", 'excuse')");
            
 
        }catch(Exception ex){
            System.out.println("Erreur lors de la cx à la base" + ex);
            System.exit(1);
        }
        return true;
    }
     
    public boolean ListerAppel(String promo, String num){
        try{
       
            String url = "jdbc:mysql://localhost:8889/absence";
            //String url = "jdbc:mysql://localhost:3306/absence";

            cx = DriverManager.getConnection(url, "root", "root");
            //cx = DriverManager.getConnection(url, "root", "");
            
            st = cx.createStatement();
            
            ResultSet rsListeEtu = st.executeQuery("SELECT * FROM promotion, etudiant, enseignant, enseigne, matiere, inscrit "
                    + "WHERE enseigne.idmatiere = matiere.idmatiere "
                    + "AND enseigne.nenseignant = enseignant.nenseignant "
                    + "AND inscrit.idmatiere = matiere.idmatiere "
                    + "AND inscrit.npromotion = promotion.npromotion "
                    + "AND etudiant.npromotion = promotion.npromotion "
                    + "AND promotion.nom = '" + promo + "' "
                    + "GROUP BY etudiant.nom ");
            
            
            int i = 0;
            while(rsListeEtu.next()){

                if(i==0){
                    promotion = new Promotion(rsListeEtu.getInt("promotion.npromotion"), 
                            rsListeEtu.getString("promotion.nom"), null,null);                  
                    i++;
                }
                
                etudiant = new Etudiant(rsListeEtu.getInt("etudiant.netudiant"), rsListeEtu.getString("etudiant.nom"), 
                        rsListeEtu.getString("etudiant.prenom"), rsListeEtu.getString("etudiant.telephone"), 
                        rsListeEtu.getString("etudiant.adresse"),rsListeEtu.getDate("etudiant.naissance"), 
                        rsListeEtu.getString("etudiant.email"), rsListeEtu.getString("etudiant.password"), 
                        promotion, null, null);
                
                promotion.getEtudiants().add(etudiant);
                
            }
            
            ResultSet rsMatiere = st.executeQuery("SELECT * FROM promotion, enseignant, enseigne, matiere, inscrit "
                    + "WHERE enseigne.idmatiere = matiere.idmatiere "
                    + "AND enseigne.nenseignant = enseignant.nenseignant "
                    + "AND inscrit.idmatiere = matiere.idmatiere "
                    + "AND inscrit.npromotion = promotion.npromotion "
                    + "AND promotion.nom = '" + promo + "' "
                    + "AND enseignant.nenseignant =" + num +"");
            
            while(rsMatiere.next()){
                
                matiere = new Matiere(rsMatiere.getInt("matiere.idmatiere"), 
                            rsMatiere.getString("matiere.nom"), null, null);
                promotion.getMatieres().add(matiere);
                
            }
            
        }catch(Exception ex){
            System.out.println("Erreur lors de la cx à la base" + ex);
            System.exit(1);
        }
        return true;
    }
    
    public boolean ListerEtu(String promo, String nenseignant, String mat){
        try{
       
            String url = "jdbc:mysql://localhost:8889/absence";
            //String url = "jdbc:mysql://localhost:3306/absence";

            cx = DriverManager.getConnection(url, "root", "root");
            //cx = DriverManager.getConnection(url, "root", "");
            
            st = cx.createStatement();
            
            ResultSet rsListeEtu = st.executeQuery("SELECT * FROM promotion, etudiant, enseignant, enseigne, matiere, inscrit, estabsent "
                    + "WHERE enseigne.idmatiere = matiere.idmatiere "
                    + "AND enseigne.nenseignant = enseignant.nenseignant "
                    + "AND inscrit.idmatiere = matiere.idmatiere "
                    + "AND inscrit.npromotion = promotion.npromotion "
                    + "AND etudiant.npromotion = promotion.npromotion "
                    + "AND estabsent.idmatiere=matiere.idmatiere "
                    + "AND estabsent.netudiant=etudiant.netudiant "
                    + "AND promotion.nom = '" + promo + "' "
                    + "AND enseignant.nenseignant = " + nenseignant + " "
                    + "AND matiere.nom= '" + mat + "'");
            
            
            int i = 0;
            while(rsListeEtu.next()){

                if(i==0){
                    
                    promotion = new Promotion(rsListeEtu.getInt("promotion.npromotion"), 
                            rsListeEtu.getString("promotion.nom"), null,null);
                    
                    matiere = new Matiere(rsListeEtu.getInt("matiere.idmatiere"), 
                            rsListeEtu.getString("matiere.nom"), null, null);
                
                    promotion.getMatieres().add(matiere);
                    
                    i++;
                }
                
                etudiant = new Etudiant(rsListeEtu.getInt("etudiant.netudiant"), rsListeEtu.getString("etudiant.nom"), 
                        rsListeEtu.getString("etudiant.prenom"), rsListeEtu.getString("etudiant.telephone"), 
                        rsListeEtu.getString("etudiant.adresse"),rsListeEtu.getDate("etudiant.naissance"), 
                        rsListeEtu.getString("etudiant.email"), rsListeEtu.getString("etudiant.password"), 
                        promotion, null,null);

                estAbsent = new EstAbsent(matiere, etudiant, rsListeEtu.getInt("estabsent.nbtotalabsence"));
                etudiant.getMatieres().add(matiere);
                etudiant.getAbsences().put(matiere, estAbsent);
                promotion.getEtudiants().add(etudiant);
                
            }
     
        }catch(Exception ex){
            System.out.println("Erreur lors de la cx à la base" + ex);
            System.exit(1);
        }
        return true;
    }
    

    public Promotion getPromotion() {
        return promotion;
    }

    public Enseignant getEnseignant() {
        return enseignant;
    }

    public Etudiant getEtudiant() {
        return etudiant;
    }

    public Matiere getMatiere() {
        return matiere;
    }

    public Cours getCours() {
        return cours;
    }

    public Enseigne getEnseigne() {
        return enseigne;
    }

    public EstAbsent getEstAbsent() {
        return estAbsent;
    }

    public Assiste getAssiste() {
        return assiste;
    }

    public Connection getCx() {
        return cx;
    }

    public void setCx(Connection cx) {
        this.cx = cx;
    }

    public Statement getSt() {
        return st;
    }

    public void setSt(Statement st) {
        this.st = st;
    }

    public Segmi getSegmi() {
        return segmi;
    }

    public void setSegmi(Segmi segmi) {
        this.segmi = segmi;
    }
     
    

    public static void main(String[] args) {
        QueryBD bd = new QueryBD();
        //bd.appelAbsent("Morel", 80, 5);
        //bd.recupAbsenceEns("5", "32012765");
        //bd.ListerEtu("miage m1c", "2", "POO");
        //bd.ConnexionEnseignant("2", "ppoi");
        //bd.ListerAppel("miage m1c", "2");
    }

}
